package com.blog.mapper;

import com.blog.pojo.TagArticleCountVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface DashboardMapper {
    /**
     * 站内文章总数
     * @return 站内文章总数
     */
    @Select("select count(*) from article")
    Integer countArticle();

    /**
     * 一周新增文章数
     * @return 新增文章数
     */
    @Select("select count(*) from article where created >= DATE_SUB(now(), interval 1 week)")
    Integer countNewArticlesThisWeek();

    /**
     * 站内评论总数
     * @return 站内评论总数
     */
    @Select("select count(*) from comment")
    Integer countComment();

    /**
     * 一周新增评论数
     * @return 新增评论数
     */
    @Select("select count(*) from comment where created >= DATE_SUB(now(), interval 1 week)")
    Integer countNewCommentsThisWeek();

    /**
     * 站内用户总数
     * @return 站内用户总数
     */
    @Select("select count(*) from user")
    Integer countUser();

    /**
     * 一周新增用户数
     * @return 新增用户数
     */
    @Select("select count(*) from user where created >= DATE_SUB(now(), interval 1 week )")
    Integer countNewUsersThisWeek();

    /**
     * 每个标签的文章数
     * @return 标签名文章数实体类
     */
    @Select("select t.tag_name as tagName, count(at.article_id) as articleCount " +
            "from article_tag at " +
            "join tag t on t.id = at.tag_id " +
            "group by t.tag_name")
    List<TagArticleCountVO> countArticlesByTag();


    /**
     * 用户文章量
     * @param userId 用户id
     * @return 用户文章量
     */
    @Select("select count(*) from article where author_id = #{userId}")
    Integer countUserArticle(Integer userId);

    /**
     * 用户一周新增文章量
     * @param userId 用户id
     * @return 用户一周新增文章量
     */
    @Select("select count(*) from article where author_id = #{userId} " +
            "and created >= DATE_SUB(now(), interval 1 week )")
    Integer countUserNewArticlesThisWeek(Integer userId);


    /**
     * 用户评论量
     * @param userId 用户id
     * @return 用户评论量
     */
    @Select("select count(c.id) from comment c " +
            "join article a on c.article_id = a.id " +
            "where a.author_id = #{userId}")
    Integer countUserComment(Integer userId);

    /**
     * 用户一周新增评论数
     * @param userId 用户id
     * @return 用户一周新增评论数
     */
    @Select("select count(c.id) from comment c " +
            "join article a on c.article_id = a.id " +
            "where a.author_id = #{userId} " +
            "and c.created >= DATE_SUB(now(), interval 1 week)")
    Integer countUserNewCommentsThisWeek(Integer userId);

    /**
     * 用户粉丝量
     * @param userId 用户id
     * @return 用户粉丝量
     */
    @Select("select count(*) from follow where user_id = #{userId}")
    Integer countFollower(Integer userId);

    /**
     * 用户点赞数
     * @param userId 用户id
     * @return 用户点赞数
     */
    @Select("select sum(support) from article where author_id = #{userId}")
    Integer sumSupport(Integer userId);
}
